﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace CIT.WMS.RM.Bll
{
    class Bll_RMIn
    {

        public static DataTable GetInStockByCondition(string condition)
        {
            string sqlcmd = string.Format(@"SELECT * FROM [dbo].[wms_T_InStockRM] WHERE 1=1 {0}", condition);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetISRMDNum(string billno, string rowno, string order, string DetailNo)
        {
            string sqlcmd = string.Format(@"SELECT * FROM [dbo].[wms_T_InStockRMRowDetails] WHERE [BillNo]='{0}' and [RowNo]='{1}' and [OrderNo]='{2}' and DetailNo='{3}'", billno, rowno, order, DetailNo);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetISRMDNum(string billno, string rowno, string DetailNo)
        {
            string sqlcmd = string.Format(@"SELECT * FROM [dbo].[wms_T_InStockRMRowDetails] WHERE [BillNo]='{0}' and [RowNo]='{1}' and DetailNo='{2}'", billno, rowno, DetailNo);

            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static bool DeleteRmRow(string billno, string rowno, string order, string DetailNo)
        {
            var result = false;
            try
            {
                var sql = string.Format(@"delete [dbo].[wms_T_InStockRMRowDetails] WHERE [BillNo]='{0}' and [RowNo]='{1}' and [OrderNo]='{2}' and DetailNo='{3}'", billno, rowno, order, DetailNo);

                var sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sql
  + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }
        public static bool UpdateRmRow(string billno, string rowno, string order)
        {
            var result = false;
            try
            {
                var sql = string.Format(@"update [dbo].[wms_T_InStockRMRows] set Status=1,Updator='{3}',UpdateTime=getdate() WHERE [BillNo]='{0}' and [RowNo]='{1}' and [OrderNo]='{2}'", billno, rowno, order, Utils.uContext.UserNamek__BackingField);

                var sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sql
  + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }


        public static DataTable GetISRMD(string billno, string rowno, string order)
        {
            string sqlcmd = string.Format(@"SELECT * FROM [dbo].[wms_T_InStockRMRowDetails] WHERE [BillNo]='{0}' and [RowNo]='{1}' and [OrderNo]='{2}' ", billno, rowno, order);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetISRMD(string billno, string rowno)
        {
            string sqlcmd = string.Format(@"SELECT * FROM [dbo].[wms_T_InStockRMRowDetails] WHERE [BillNo]='{0}' and [RowNo]='{1}'", billno, rowno);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetMaxDetailNo(string billno, string rowno, string order)
        {
            string sqlcmd = string.Format(@"SELECT  * FROM [dbo].[wms_T_InStockRMRowDetails] WHERE [BillNo]='{0}' and [RowNo]='{1}' and [OrderNo]='{2}'order by DetailNo; ", billno, rowno, order);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable QueryLocation(string locationSN)
        {
            string sqlcmd = string.Format(@"select a.Location_SN,b.Area_SN,c.Storage_SN from T_Bllb_StorageLocation_tbsl a 
left join T_Bllb_StorageArea_tbsa b on a.Area_SN = b.Area_SN
 left join T_Bllb_Storage_tbs c on b.Storage_SN = c.Storage_SN
 LEFT JOIN wms_T_StorageRegion d ON c.Region_SN=d.Region_SN
 WHERE d.Region_Name='{0}' AND a.Location_SN='{1}' ", Utils.uContext.CompanyNamek__BackingField, locationSN);

            var result = Utils.NMS.QueryDataTable(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0]);
            return result;
        }

        public static bool IsStandard(string partNumber, string location)
        {
            var standard = GetPtAreaStandard(partNumber);
            if (standard == "")
            {
                return true;
            }
            string sqlcmd = string.Format(@"SELECT * FROM  [dbo].[T_Bllb_StorageLocation_tbsl] where [Area_Standard]='{0}' and [Location_SN]='{1}'", standard, location);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            if (result.Rows.Count == 0)
            {
                return false;
            }
            return true;
        }

        public static string GetPtAreaStandard(string partNumber)
        {
            string sqlcmd = string.Format(@"SELECT * FROM  [dbo].[wms_R_PtAreaStandard] where [PartNumber]='{0}'", partNumber);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            if (result.Rows.Count > 0)
            {
                return result.Rows[0]["AreaStandard"].ToString();
            }
            return "";
        }

        /// <summary>
        /// 获取该表单行号未完成入库单详细
        /// </summary>
        /// <param name="billno"></param>
        /// <param name="rowno"></param>
        /// <param name="orderno"></param>
        /// <returns></returns>
        public static DataTable GetRMDetail(string billno, string rowno)
        {
            string sqlcmd = string.Format(@"SELECT * FROM  [dbo].[wms_T_InStockRMRowDetails] WHERE   Status<>'9' and BillNo='{0}' and RowNo='{1}' ;", billno, rowno);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }


        public static DataTable GetRMRowsAll(string billno, string rowno)
        {
            string sqlcmd = string.Format(@"SELECT * FROM  [dbo].[wms_T_OutStockRMRows] WHERE   BillNo='{0}' and RowNo='{1}' ;", billno, rowno);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }
        /// <summary>
        /// 修改采购入库行表状态为完成
        /// </summary>
        /// <param name="billNo"></param>
        /// <param name="rowno"></param>
        /// <returns></returns>
        public static bool UpdateRMRow(string billNo, string rowno, string orderno)
        {
            var result = false;
            try
            {
                var sql = string.Format(@"UPDATE [dbo].[wms_T_InStockRMRows] SET  Status='9',Updator='{0}',
UpdateTime=GETDATE() WHERE BillNo='{1}' and RowNo='{2}'and OrderNo='{3}';",
Utils.uContext.UserNamek__BackingField, billNo, rowno, orderno);

                var sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sql
  + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }

        /// <summary>
        /// 获取该表单行号未完成入库单行
        /// </summary>
        /// <param name="billno"></param>
        /// <param name="rowno"></param>
        /// <param name="orderno"></param>
        /// <returns></returns>
        public static DataTable GetRMRow(string billno)
        {
            string sqlcmd = string.Format(@"SELECT * FROM  [dbo].[wms_T_InStockRMRows] WHERE  Status<>'9' and RealQty<>'0' and BillNo='{0}';", billno);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }


        public static DataTable GetRMRowByCondition(string billno, string RowNo, string order)
        {
            string sqlcmd = string.Format(@"select a.*,FGQty from wms_T_InStockRMRows a LEFT JOIN wms_T_IQC b on a.BillNo=b.BillNo and a.RowNo=b.RowNo and a.OrderNo=b.OrderNo where a.BillNo='{0}' and a.RowNo='{1}' and a.OrderNo='{2}';;", billno, RowNo, order);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetRMRowsByCondition(string billno, string RowNo, string order)
        {
            string sqlcmd = string.Format(@"select  *  from wms_T_InStockRMRows   where  BillNo='{0}' and  RowNo='{1}' and OrderNo='{2}';;", billno, RowNo, order);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }


        /// <summary>
        /// 修改采购入库行表状态为完成
        /// </summary>
        /// <param name="billNo"></param>
        /// <param name="rowno"></param>
        /// <returns></returns>
        public static bool UpdateRM(string billNo)
        {
            var result = false;
            try
            {
                var sql = string.Format(@"UPDATE [dbo].[wms_T_InStockRM] SET  Status='9',Updator='{0}',
                                            UpdateTime=GETDATE() WHERE BillNo='{1}';",
                                            Utils.uContext.UserNamek__BackingField, billNo);

                var sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sql
 + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }


        public static bool UpdateRMPR(string billNo)
        {
            var result = false;
            try
            {
                var sql = string.Format(@"UPDATE [dbo].[wms_T_InStockRM] SET  Status='6',Updator='{0}',
                                            UpdateTime=GETDATE() WHERE BillNo='{1}';",
                                            Utils.uContext.UserNamek__BackingField, billNo);

                var sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sql
 + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }

        public static DataTable GetRM(string billno)
        {
            string sqlcmd = string.Format(@"SELECT * FROM  [dbo].[wms_T_InStockRM] WHERE  Status='5' and  BillNo='{0}' and (BillType='采购入库' or BillType='进口采购入库' or BillType='车载进口采购' or BillType='分包入库' or BillType='原材料销售' or BillType='原材料分包');", billno);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetRMTL(string billno)
        {
            string sqlcmd = string.Format(@"SELECT * FROM  [dbo].[wms_T_InStockRM] WHERE  Status='5' and  BillNo='{0}' and   (BillType='退料入库' or BillType='非拉动退料入库');", billno);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetIsType201(string Storage_SN)
        {
            string sqlcmd = string.Format(@"select * from [dbo].[T_Bllb_Storage_tbs] where Storage_Type='201' and Storage_SN='{0}';", Storage_SN);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetInventoryBySN(string Location_SN)
        {
            string sqlcmd = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE Location_SN='{0}'", Location_SN);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }


        public static DataTable GetInventory(string Location_SN, string PartNumber)
        {
            string sqlcmd = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE Location_SN='{0}' and PartNumber='{1}'", Location_SN, PartNumber);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        /// <summary>
        /// 采购入库
        /// </summary>
        /// <param name="qty"></param>
        /// <param name="billno"></param>
        /// <param name="rowno"></param>
        /// <param name="orderno"></param>
        /// <param name="Storage_SN"></param>
        /// <param name="Area_SN"></param>
        /// <param name="Location_SN"></param>
        /// <param name="version"></param>
        /// <param name="datecode"></param>
        /// <param name="lotno"></param>
        /// <param name="PartNumber"></param>
        /// <param name="RelatedBill"></param>
        /// <returns></returns>
        public static bool ProductInStockRM(decimal qty, string billno, string rowno, string orderno, string storageSN, string areaSN, string locationSN, string version, string datecode, string lotno, string PartNumber, string RelatedBill, string DetailNo, bool InvenNew)
        {
            var result = false;
            try
            {
                var sql = string.Format(@"UPDATE [dbo].[wms_T_InStockRMRowDetails] SET RealQty={0},Status='9',Updator='{1}',
UpdateTime=GETDATE(),Storage_SN='{5}',Area_SN='{6}',Location_SN='{7}' WHERE BillNo='{2}' and RowNo='{3}' and OrderNo='{4}';", qty,
Utils.uContext.UserNamek__BackingField, billno, rowno, orderno, storageSN, areaSN, locationSN);
                string sql2 = "";
                string insert_Sql = "";

                if (string.IsNullOrEmpty(lotno))
                {
                    lotno = datecode;
                }
                // var sqlInv = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE Location_SN='{0}'", locationSN);

                //var invDt = Utils.NMS.GetDataTable(sqlInv);
                var sqlRow = string.Format(@"SELECT SupplyNo,Supplier,ToolRemark,Updator3 FROM wms_T_InStockRMRows Row inner join wms_T_InStockRM RM on Row.BillNo=RM.BillNo WHERE Row.BillNo='{0}' ", billno);
                var rowDt = Utils.NMS.GetDataTable(sqlRow);
                if (rowDt.Rows.Count == 0)
                {
                    return false;
                }
                //扩展项
                var extSupplier = rowDt.Rows[0]["Supplier"].ToString();
                var extTool = rowDt.Rows[0]["ToolRemark"].ToString();
                var extQty = qty;
                var extSupplyNo = "";
                if (rowDt.Rows[0]["SupplyNo"] != DBNull.Value)
                {
                    extSupplyNo = rowDt.Rows[0]["SupplyNo"].ToString();
                }
                var strExt = "ExtSupplier='" + extSupplier + "',ExtTool='" + extTool + "',ExtQty=" + extQty + ",ExtSupplyNo='" + extSupplyNo + "',";
                var IQC = rowDt.Rows[0]["Updator3"].ToString();

                var sqlDel = string.Format(@"DELETE FROM dbo.wms_T_Inventory WHERE Location_SN='{0}';", billno);

                if (!InvenNew)
                {
                    insert_Sql = string.Format(@"UPDATE dbo.wms_T_Inventory SET " + strExt + "Qty=ISNULL(Qty,0)+{1} WHERE Location_SN='{0}' and PartNumber='{2}';", locationSN, qty, PartNumber);
                }
                else
                {
                    sql2 = string.Format(@"UPDATE T_Bllb_StorageLocation_tbsl SET Status_Flag='1' WHERE Location_SN='{0}';", locationSN);
                    insert_Sql = string.Format(@"INSERT INTO [dbo].[wms_T_Inventory]
                                               ([Guid]
                                               ,[Storage_SN]
                                               ,[Area_SN]
                                               ,[Location_SN]
                                               ,[PartNumber]
                                               ,[Qty]
                                               ,[EnterTime]
                                               ,[Status],Version,DateCode,LotNo,ExtSupplier,ExtTool,ExtQty,ExtSupplyNo)
                                         VALUES
                                               ('{0}'
                                               ,'{1}'
                                               ,'{2}'
                                               ,'{3}'
                                               ,'{4}'
                                               ,'{5}'
                                               ,GETDATE()
                                               ,'{6}','{7}','{8}','{9}','{10}','{11}',{12},'{13}');", Guid.NewGuid(), storageSN, areaSN, locationSN, PartNumber, qty, "1", version, datecode, lotno,
                                                                                               extSupplier, extTool, extQty, extSupplyNo);
                }
                string insertHis_Sql = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO],RelatedBill1,RelatedBill2,LotNo)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,GETDATE()
           ,'{7}','{8}','{9}','{10}');", Guid.NewGuid(), storageSN, areaSN, locationSN, PartNumber,
                        qty, Utils.uContext.UserNamek__BackingField, "I", RelatedBill, billno, lotno);

                string sqlUpdStatus = @"UPDATE [dbo].[wms_T_InStockRM] SET Status='9',Updator='{0}',
                                        UpdateTime=GETDATE() WHERE  BillNo='{1}' AND
                                        (SELECT count(0) FROM wms_T_InStockRMRowDetails WHERE STATUS <> '9' AND BillNo = '{1}') = 0;";
                sqlUpdStatus = string.Format(sqlUpdStatus, Utils.uContext.UserNamek__BackingField, billno);

                //***留样处理 START
                //获取成本中心
                var sqlCt = string.Format(@"SELECT * FROM wms_S_Option WHERE OptCode='{0}' ", "QCenterCode");
                var dtCt = Utils.NMS.GetDataTable(sqlCt);
                var centerCode = dtCt.Rows[0]["OptValue"].ToString();
                //获取留样数量
                sqlCt = string.Format(@"SELECT * FROM wms_T_IQC WHERE BillNo='{0}' ", billno);
                dtCt = Utils.NMS.GetDataTable(sqlCt);
                var checkQty = Convert.ToDecimal(dtCt.Rows[0]["CheckQty"]);

                if (checkQty > 0 && checkQty != qty)
                {
                    var sqlRst = string.Format("select * from wms_T_Request where BillNo='{0}'", billno);
                    var dtRst = Utils.NMS.GetDataTable(sqlRst);
                    if (dtRst.Rows.Count == 0)
                    {
                        //非生产领料
                        sqlCt = string.Format(@"insert into wms_T_Request(Guid,BillNo,Storage_SN,Area_SN,Location_SN,CenterCode,BillType,BillSeries,
                                    Qty,PartNumber,Remark,Creator,Version,CreateTime,RowNo,RelatedBill,Status) VALUES 
                                    (NEWID(),'{11}',
                                    '{0}','{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}','{9}','{10}',GETDATE(),1,' ','1');",
                                           storageSN, areaSN, locationSN, centerCode, "OUS", "WH1", checkQty, PartNumber, ' ', IQC, version, billno);
                    }
                }
                else
                {
                    sqlCt = "";
                }
                //***留样处理 END
                var sqlCmd = (sql + sql2 + sqlDel + insert_Sql + insertHis_Sql + sqlUpdStatus + sqlCt).TrimEnd(';');

                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
 + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    Bll_CenterCode.QCenterCode(billno, Utils.uContext.UserNamek__BackingField, locationSN, checkQty);
                    result = true;
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }


        public static bool ProductInStockPRRM(decimal qty, string billno, string rowno, string orderno, string storageSN, string areaSN, string locationSN, string version, string datecode, string lotno, string PartNumber, string RelatedBill, string DetailNo, bool inveNEW)
        {
            var result = false;
            try
            {
                var sql = string.Format(@"UPDATE [dbo].[wms_T_InStockRMRowDetails] SET RealQty={0},Status='9',Updator='{1}',
UpdateTime=GETDATE(),Storage_SN='{5}',Area_SN='{6}',Location_SN='{7}' WHERE BillNo='{2}' and RowNo='{3}' and OrderNo='{4}' and DetailNo='{8}';", qty,
Utils.uContext.UserNamek__BackingField, billno, rowno, orderno, storageSN, areaSN, locationSN, DetailNo);
                string sql2 = "";
                string insert_Sql = "";

                if (string.IsNullOrEmpty(lotno))
                {
                    lotno = datecode;
                }
                //var sqlInv = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE Location_SN='{0}'", locationSN);

                //var invDt = Utils.NMS.GetDataTable(sqlInv);
                var sqlDel = string.Format(@"DELETE FROM dbo.wms_T_Inventory WHERE Location_SN='{0}';", billno);

                if (!inveNEW)
                {
                    insert_Sql = string.Format(@"UPDATE dbo.wms_T_Inventory SET Qty=ISNULL(Qty,0)+{1} WHERE Location_SN='{0}' and PartNumber='{2}';", locationSN, qty, PartNumber);
                }
                else
                {
                    sql2 = string.Format(@"UPDATE T_Bllb_StorageLocation_tbsl SET Status_Flag='1' WHERE Location_SN='{0}';", locationSN);
                    insert_Sql = string.Format(@"INSERT INTO [dbo].[wms_T_Inventory]
                                               ([Guid]
                                               ,[Storage_SN]
                                               ,[Area_SN]
                                               ,[Location_SN]
                                               ,[PartNumber]
                                               ,[Qty]
                                               ,[EnterTime]
                                               ,[Status],Version,DateCode,LotNo)
                                         VALUES
                                               ('{0}'
                                               ,'{1}'
                                               ,'{2}'
                                               ,'{3}'
                                               ,'{4}'
                                               ,'{5}'
                                               ,GETDATE()
                                               ,'{6}','{7}','{8}','{9}');", Guid.NewGuid(), storageSN, areaSN, locationSN, PartNumber, qty, "1", version, datecode, lotno);
                }
                string insertHis_Sql = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO],RelatedBill1,RelatedBill2,LotNo)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,GETDATE()
           ,'{7}','{8}','{9}','{10}');", Guid.NewGuid(), storageSN, areaSN, locationSN, PartNumber,
                        qty, Utils.uContext.UserNamek__BackingField, "I", RelatedBill, billno, lotno);

                var sqlCmd = (sql + sql2 + sqlDel + insert_Sql + insertHis_Sql).TrimEnd(';');

                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
 + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }



        public static DataTable GetPRInfo(string billno)
        {
            string sqlcmd = string.Format(@"select TI.BillNo,TI.BillType,TI.RelatedBill,TI.Supplier,TI.Storage_SN,TIR.PartNumber,TIR.RowNo,TIR.OrderNo,TIR.PlanQty,TIR.RealQty,TIR.Unit from [dbo].[wms_T_InStockRM] TI inner join [dbo].[wms_T_InStockRMRows] TIR on TI.BillNo=TIR.BillNo WHERE TI.BillNo='{0}' and TI.BillType='退料入库';", billno);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetOutDetail(string UniqueCode)
        {
            string rowSql = string.Format(@"select * from wms_T_OutStockRMRowDetails where UniqueCode='{0}'", UniqueCode);
            DataTable rowDT = Utils.NMS.GetDataTable(rowSql);

            return rowDT;
        }


        public static DataTable GetRecommendLocationSN(string productNumber, string datacode)
        {
            string rowSql = string.Format(@"SELECT * FROM [dbo].[wms_T_Inventory]  where PartNumber='{0}' and DateCode='{1}'", productNumber, datacode);

            DataTable rowDT = Utils.NMS.GetDataTable(rowSql);

            return rowDT;
        }

        public static DataTable GetGroupERPNo()
        {
            string rowSql = string.Format(@"SELECT RelatedBill FROM  wms_T_InStockRM  where Status='4' group by RelatedBill,CreateTime order by CreateTime desc");

            DataTable rowDT = Utils.NMS.GetDataTable(rowSql);

            return rowDT;
        }

        public static DataTable GetGroupPartNumberByERPNo(string RelatedBill)
        {
            string rowSql = string.Format(@"SELECT * FROM wms_T_InStockRMRows InSR inner join wms_T_InStockRM InS on InSR.BillNo=InS.BillNo where InS.Status='4' and InS.RelatedBill='{0}'", RelatedBill);

            DataTable rowDT = Utils.NMS.GetDataTable(rowSql);

            return rowDT;
        }

        public static DataTable GetGroupPartNumberByRela(string RelatedBill)
        {
            string rowSql = string.Format(@"SELECT * FROM wms_T_InStockRMRows InSR inner join wms_T_InStockRM InS on InSR.BillNo=InS.BillNo where InS.Status='4' and InS.RelatedBill='{0}'", RelatedBill);

            DataTable rowDT = Utils.NMS.GetDataTable(rowSql);

            return rowDT;
        }

        public static DataTable GetNoGroupRows(string billno)
        {
            string rowSql = string.Format(@"select a.* from wms_T_InStockRMRows a
                    left join wms_T_IQC b on a.BillNo = b.BillNo and a.RowNo = b.RowNo and a.OrderNo = b.OrderNo
                    where a.BillNo='{0}' and a.RealQty!=0 and a.Status!=2 and b.FGQty!=0;", billno);

            DataTable rowDT = Utils.NMS.GetDataTable(rowSql);

            return rowDT;
        }

        public static DataTable GetBillInfoByCondition(string billno, string rowno, string orderNo)
        {
            string rowSql = string.Format(@"SELECT InS.Storage_SN,b.FGQty, InSR.RelatedBill,InSR.PlanQty,(select isnull(sum(PlanQty),0) from wms_T_InStockRMRowDetails where BillNo=InSR.BillNo and RowNo=InSR.RowNo and OrderNo=InSR.OrderNo) as GroupSum FROM wms_T_InStockRMRows InSR inner join wms_T_InStockRM InS on InSR.BillNo=InS.BillNo  left join wms_T_IQC b on InSR.BillNo = b.BillNo and InSR.RowNo = b.RowNo and InSR.OrderNo = b.OrderNo
 where InS.Status='4' and InSR.BillNo='{0}' and InSR.RowNo='{1}' and InSR.OrderNo='{2}'", billno, rowno, orderNo);

            DataTable rowDT = Utils.NMS.GetDataTable(rowSql);

            return rowDT;
        }

        public static DataTable GetGroupRowsByCondition(string RelatedBill, string PartNumber)
        {
            string rowSql = string.Format(@"SELECT *,(select isnull(sum(PlanQty),0) from wms_T_InStockRMRowDetails where BillNo=InSR.BillNo and RowNo=InSR.RowNo and OrderNo=InSR.OrderNo) as GroupSum FROM wms_T_InStockRMRows InSR inner join wms_T_InStockRM InS on InSR.BillNo=InS.BillNo where InS.Status='4' and InS.RelatedBill='{0}' and InSR.PartNumber='{1}'", RelatedBill, PartNumber);

            DataTable rowDT = Utils.NMS.GetDataTable(rowSql);

            return rowDT;
        }


        public static DataTable GetIsNoFG(string billno)
        {
            string rowSql = string.Format(@"select a.* from wms_T_InStockRMRows a
                    left join wms_T_IQC b on a.BillNo = b.BillNo and a.RowNo = b.RowNo and a.OrderNo = b.OrderNo
                    where a.BillNo='{0}' and a.RealQty!=0 and a.Status!=2 and b.FGQty=0;", billno);

            DataTable rowDT = Utils.NMS.GetDataTable(rowSql);

            return rowDT;
        }

        public static bool UpdateGroupStatus(string billNo)
        {
            var result = false;
            try
            {
                var sql = string.Format(@"Update wms_T_InStockRM set Status=5,Updator='{0}',UpdateTime=getdate() where BillNo='{1}' ;",
                                            Utils.uContext.UserNamek__BackingField, billNo);

                var sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sql
 + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }

        public static bool UpdateGroupStatus(string billNo, string status)
        {
            var result = false;
            try
            {
                var sql = string.Format(@"Update wms_T_InStockRM set Status='{2}',Updator='{0}',UpdateTime=getdate() where BillNo='{1}' ;",
                                            Utils.uContext.UserNamek__BackingField, billNo, status);

                var sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sql
 + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }



        public static bool InsertNewDetail(string billNo, string rowno, string orderno, int count, string dateCode, string area_Standard, string RelatedBill, string PartNumber, string Version, string PlanQty, string Unit, string Storage_SN, decimal DetailNo)
        {
            var result = false;
            try
            {
                string sql = "";
                for (int i = 0; i < count; i++)
                {
                    sql = sql + string.Format(@"insert into wms_T_InStockRMRowDetails (BillNo,RelatedBill,PartNumber,RowNo,OrderNo,DetailNo,Version,DateCode,Area_Standard,PlanQty,Unit,Creator,Storage_SN,CreateTime,RealQty,Status) values('{0}','{1}','{2}',{3},{4},'{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}',getdate(),0,1);", billNo, RelatedBill, PartNumber, rowno, orderno, DetailNo + i + 1, Version, dateCode, area_Standard, PlanQty, Unit, Utils.uContext.UserNamek__BackingField, Storage_SN);
                }
                var sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sql
  + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }


        public static bool UpdateInStockDetail(string billNo, string rowNo, string orderNo, string detailNo, string dateCode, string Plan)
        {
            var result = false;
            try
            {
                var sql = string.Format(@"Update wms_T_InStockRMRowDetails set DateCode='{0}',PlanQty='{1}',Updator='{2}', UpdateTime=getdate() where BillNo='{3}' and  RowNo='{4}' and OrderNo='{5}' and DetailNo='{6}';", dateCode, Plan,
                                            Utils.uContext.UserNamek__BackingField, billNo, rowNo, orderNo, detailNo);

                var sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sql
 + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sql);

                if (excRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }



        public static bool UpdateInStockRow(string billNo, string rowNo, string orderNo, string status)
        {
            var result = false;
            try
            {
                var sql = string.Format(@"Update wms_T_InStockRMRows set Status='{4}',Updator='{3}',UpdateTime=getdate() where BillNo='{0}' and RowNo='{1}' and OrderNo='{2}';", billNo, rowNo, orderNo,
                                            Utils.uContext.UserNamek__BackingField, status);

                var sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sql
 + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sql);

                if (excRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }
    }

}
